package edu.princeton.cogsci.wn.sql;

import edu.princeton.cogsci.wn.*;
import edu.princeton.cogsci.wn.sql.SQLSynsetKey;
import java.sql.*;
import java.util.*;

public class SQLWordNetImpl extends WordNetImpl
                                      implements Constants
{
  public Connection mysqlConnection;
  public Hashtable synsetTable;
  public Hashtable wordSenseTable;

  public static void main( String[] args )
  {
    try
    {
      WordNetImpl wnimp = new SQLWordNetImpl( );

    }
    catch ( WordNetException e )
    {
      e.printStackTrace( );
    }
  }

  public String getTitle( )
  {
    return "SQL wni v0.6";
  }

  public SQLWordNetImpl( )
    throws WordNetException
  {
    this( "localhost", "drudge", "ascii2sql", "drudge", 0 );
  }

  public SQLWordNetImpl( String host, String user, String pass, String db, int port )
    throws WordNetException
  {
    if ( host == null )
      host = "dream.princeton.edu";
    if ( user == null )
      user = "wn";
    if ( db == null )
      db = "wn";
    
    synsetTable = new Hashtable( );
    wordSenseTable = new Hashtable( );

    try
    {
      new org.gjt.mm.mysql.Driver( );
    }
    catch ( SQLException e )
    {
      throw new WordNetException( "Unable to load sql driver" );
    }
    try
    {
      String connectionStr = "jdbc:mysql://" + host;
      if ( port != 0 )
        connectionStr += ":" + port;
      connectionStr += "/" + db + "?user=" + user;
      if ( pass != null )
        connectionStr += "&password=" + pass;

      mysqlConnection = DriverManager.getConnection( connectionStr );
    }
    catch ( SQLException e )
    {
      System.out.println( e );
      throw new WordNetException( "Unable to connect to mysql database: " + e );
    }

    WordNetImpl.setCurrentWNI( this );
  }

  public void closeSQLConnection( )
  {
    try
    {
      mysqlConnection.close( );
    }
    catch ( Exception e )
    {

    }
  }

  public WordSenseKey lookupWordSenseKey( String word, int pos, int sense )
    throws WordNetException
  {
    try
    {
      Statement stmt = mysqlConnection.createStatement( );

      String query = "select ( WordSenses.WordSenseKey ) from ( Synsets, WordSenses )"
                      + "where WordSenses.Form='" + word + "' && WordSenses.Sense="
                      + sense + " && "
                      + "Synsets.SynsetKey = WordSenses.SynsetKey && Synsets.Pos="
                      + pos;

      ResultSet res = stmt.executeQuery( query );

      if ( res.next( ) )
      {
        int key = res.getInt( "WordSenses.WordSenseKey" );
        res.close( );
        stmt.close( );
        return new SQLWordSenseKey( key );
      }

      res.close( );
      stmt.close( );
      throw new WordNetException( "WordSenseKey for (" + word + "," + pos + "," + sense
                                  + ") not found" );
    }
    catch ( SQLException e )
    {
      throw new WordNetException( "Unable to query database: " + e );
    }
  }

  public SynsetKey lookupSynsetKey( String word, int pos, int sense )
    throws WordNetException
  {
    try
    {
      Statement stmt = mysqlConnection.createStatement( );

      String query = "select WordSenses.SynsetKey from WordSenses, Synsets "
                      + " where WordSenses.Form='" + word + "' && WordSenses.Sense="
                      + sense + " && Synsets.Pos=" + pos
                      + " && ( Synsets.SynsetKey=WordSenses.SynsetKey )";
      ResultSet res = stmt.executeQuery( query );

      if ( res.next( ) )
      {
        int key = res.getInt( "SynsetKey" );
        res.close( );
        stmt.close( );
        return new SQLSynsetKey( key );
      }

      res.close( );
      stmt.close( );
      throw new WordNetException( "SynsetKey for (" + word + "," + pos + "," + sense
                                  + ") not found" );
    }
    catch ( SQLException e )
    {
      throw new WordNetException( "Unable to query database: " + e );
    }
  }

  public WordSense lookupWordSense( WordSenseKey key )
    throws WordNetException
  {
    WordSense ws = ( WordSense )wordSenseTable.get( key );
    if ( ws != null )
      return ws;
    try
    {
      Statement stmt = mysqlConnection.createStatement( );
      String query = "select * from WordSenses where WordSenseKey=" + ( ( SQLWordSenseKey )key ).key;

      ResultSet res = stmt.executeQuery( query );

      if ( res.next( ) )
      {
        ws = new WordSense( );
	ws.setForm( res.getString( "Form" ) );
	ws.setSense( res.getInt( "Sense" ) );
	ws.setPolysemy( res.getInt( "Polysemy" ) );
	ws.setFrequency( res.getInt( "Frequency" ) );
	ws.setKey( key );
        try
        {
          ws.setSynset( lookupSynset( new SQLSynsetKey( res.getInt( "SynsetKey" ) ) ) );
        }
        catch ( WordNetException e )
        {
          ws.setSynset( null );
        }
	res.close( );
	stmt.close( );
	wordSenseTable.put( key, ws );
	return ws;
      }
      throw new WordNetException( "WordSenseKey " + ( ( SQLWordSenseKey )key ).key + " not found" );
    }
    catch ( SQLException e )
    {
      throw new WordNetException( "Unable to query database: " + e );
    }
  }

  public Synset lookupSynset( SynsetKey key )
    throws WordNetException
  {
    Synset ss = ( Synset )synsetTable.get( key );
    if ( ss != null )
      return ss;
    try
    {
      Statement stmt = mysqlConnection.createStatement( );
      int k = ( ( SQLSynsetKey )key ).key;
      String query = "select * from Synsets where SynsetKey=" + k;

      ResultSet res = stmt.executeQuery( query );

      if ( res.next( ) )
      {
	ss = new Synset( );

	String posString = res.getString( "Pos" );

	ss.setPos( Constants.NOUN );
	if ( posString.equals( "Noun" ) )
	     ss.setPos( Constants.NOUN );
	if ( posString.equals( "Verb" ) )
	     ss.setPos( Constants.VERB );
	if ( posString.equals( "Adjective" ) )
	     ss.setPos( Constants.ADJECTIVE );
	if ( posString.equals( "Adverb" ) )
	     ss.setPos( Constants.ADVERB );

        synsetTable.put( key, ss );

	ss.setGloss( res.getString( "Gloss" ) );
	ss.setCategory( res.getInt( "Category" ) );
	ss.setKey( key );

        try
        {
          String query2 = "select ( WordSenseKey ) from WordSenses where SynsetKey=" + k;
          ResultSet res2 = stmt.executeQuery( query2 );
          Vector synonyms = new Vector( );
          while ( res2.next( ) )
          {
            WordSenseKey wskey = new SQLWordSenseKey( res2.getInt( "WordSenseKey" ) );
            synonyms.add( lookupWordSense( wskey ) );
          }
	  res2.close( );
          ss.setSynonyms( new WordSense[synonyms.size( )] );
          for ( int i = 0; i < synonyms.size( ); i++ )
          {
            ss.getSynonyms( )[i] = ( WordSense )synonyms.get( i );
          }
	  res.close( );
        }
        catch ( SQLException e )
        {
          throw new WordNetException( "Unable to query database" );
        }
        catch ( WordNetException ee )
        {
          throw new WordNetException( "Unable to resolve synonyms" );
        }

        try
        {
          String query3 = "select * from SemanticRelations where SynsetKey1=" + k + " or SynsetKey2=" + k;

          ResultSet res3 = stmt.executeQuery( query3 );
          Vector vec = new Vector( );
	  while ( res3.next( ) )
          {
            int type = res3.getInt( "Relation" );
            SynsetKey key1 = new SQLSynsetKey( res3.getInt( "SynsetKey1" ) );
            SynsetKey key2 = new SQLSynsetKey( res3.getInt( "SynsetKey2" ) );

            SemanticRelation rel;
	    if ( ( ( SQLSynsetKey )key2 ).key == k )
            {
              switch ( type )
              {
                case HYPERNYM:
                  type = HYPONYM;
                  break;
                case MERONYM:
                  type = HOLONYM;
                  break;
              }
              rel = new SemanticRelation( type, key1 );
            }
            else
              rel = new SemanticRelation( type, key2 );

	    boolean addIt = true;

	    for ( int i = 0; i < vec.size( ); i++ )
	    {
	      SemanticRelation reli = ( SemanticRelation )vec.get( i );
	      if ( reli.equals( rel ) )
	        addIt = false;
	    }
	    
	    if ( addIt )
              vec.add( rel );
          }

          SemanticRelation[] rels = new SemanticRelation[vec.size( )];
          for ( int i = 0; i < rels.length; i++ )
            rels[i] = ( SemanticRelation )vec.get( i );
          ss.setRelations( rels );
        }
        catch ( SQLException e )
        {
          throw new WordNetException( "Unable to resolve relations: " + e );
        }
	stmt.close( );
        return ss;
      }
      throw new WordNetException( "SynsetKey " + k + " not found" );
    }
    catch ( SQLException e )
    {
      throw new WordNetException( "Unable to query database: " + e );
    }
  }

  public Synset[] lookupSynsetByGloss( String keyword, int gloss )
  {
    throw new UnsupportedOperationException( "WordNetImpl.lookupSynsetByGloss" );
  }

  public WordSenseKey lookupWordSenseKey( String key )
  {
    throw new UnsupportedOperationException( "WordNetImpl.lookupWordSenseKey" );

  }

  public Synset[] lookupSynsetAllSenses( String word, int pos )
    throws WordNetException
  {
    try
    {
      Statement stmt = mysqlConnection.createStatement( );
    
      String query = "select Synsets.SynsetKey from Synsets, WordSenses where Synsets.SynsetKey=WordSenses.SynsetKey && Synsets.Pos=" + pos + " && WordSenses.form='" + word + "'";

      ResultSet res = stmt.executeQuery( query );

      Vector vec = new Vector( );
      while ( res.next( ) )
      {
	SynsetKey key = new SQLSynsetKey( res.getInt( "Synsets.SynsetKey" ) );
	Synset ss = lookupSynset( key );
        vec.add( ss );
      }

      Synset[] synsets = new Synset[vec.size( )];
      for ( int i = 0; i < vec.size( ); i++ )
        synsets[i] = ( Synset )vec.get( i );
    
      res.close( );
      stmt.close( );

      return synsets;
    }
    catch ( SQLException e )
    {
      throw new WordNetException( "Unable to query database: " + e );
    }
  }

  public SynsetKey lookupSynsetKey( String canonicalNumberSynsetKeyDataVariableBytes )
  {
    return new SQLSynsetKey( Integer.parseInt( canonicalNumberSynsetKeyDataVariableBytes ) );
  }

  public String[] lookupBaseForms( String word )
  {
    try
    {
      Statement stmt = mysqlConnection.createStatement( );
      
      String query = "select BaseForm from BaseFormExceptions where InflectedForm='" + word + "'";

      ResultSet res = stmt.executeQuery( query );

      Vector vec = new Vector( );
      while ( res.next( ) )
        vec.add( res.getString( "BaseForm" ) );

      res.close( );
      stmt.close( );

      if ( vec.size( ) != 0 )
      {
        String[] exps = new String[vec.size( )];
        for ( int i = 0; i < exps.length; i++ )
          exps[i] = ( String )vec.get( i );
        return exps;
      }
      
      return new String[0];
    }
    catch ( SQLException e )
    {
      throw new WordNetException( "Unable to query database: " + e );
    }
  }

  private int getUserID( String user, String pass )
    throws SQLException
  {
    Statement stmt = mysqlConnection.createStatement( );
    ResultSet res = stmt.executeQuery( "select editor_id from wneditors where user_name='" + user + "' && password=password('" + pass + "')" );
    if ( !res.next( ) )
    {
      res.close( );
      stmt.close( );
      return -1;
    }

    int eID = res.getInt( "editor_id" );

    res.close( );
    stmt.close( );
    
    return eID;
  }

  private boolean isFlagOn( String flag, int eID )
    throws SQLException
  {
    Statement stmt = mysqlConnection.createStatement( );
    ResultSet res = stmt.executeQuery( "select " + flag + " from wneditors where editor_id=" + eID );
    if ( !res.next( ) )
    {
      res.close( );
      stmt.close( );
      return false;
    }

    boolean ok = res.getBoolean( flag );

    res.close( );
    stmt.close( );

    return ok;
  }

  public void updateSynset( SynsetKey sk, Synset ss, String user, String pass )
    throws WordNetEditException
  {
    try
    {
      Statement stmt = mysqlConnection.createStatement( );

      int eID = getUserID( user, pass );

      if ( eID == -1 )
      {
	stmt.close( );
        throw new WordNetEditException( "Bad username/password" );
      }

      if ( !isFlagOn( "flag_update", eID ) )
      {
	stmt.close( );
        throw new WordNetEditException( "User cannot update" );
      }

      SQLSynsetKey sqlsk = ( SQLSynsetKey )sk;

      ResultSet res = stmt.executeQuery( "select Owner from Synsets where SynsetKey=" + sqlsk.key + " && Owner=" + eID );
      if ( res.next( ) )
      { 
	res.close( );
        String query = "update Synsets set Gloss='" + ss.getGloss( ) + "', Pos=" + ss.getPos( ) + ", Category=" + ss.getCategory( ) + "  where SynsetKey=" + sqlsk.key + " && Owner=" + eID;
	stmt.executeQuery( query ).close( );	
	stmt.close( );

        for ( int i = 0; i < ss.getSynonyms( ).length; i++ )
          updateWordSense( ss.getSynonyms( )[i], eID );
        for ( int i = 0; i < ss.getRelations( ).length; i++ )
          updateSemanticRelation( ss, ss.getRelations( )[i], eID );

        return;
      }

      res = stmt.executeQuery( "select Owner from Synsets where SynsetKey=" + sqlsk.key );

      if ( res.next( ) )
      {
        res.close( );
        String query = "insert into Synsets ( Owner, SynsetKey, Gloss, Pos, Category ) values ( " + eID + ", " + sqlsk.key + ", '" + ss.getGloss( ) + "', " + ss.getPos( ) + ", " + ss.getCategory( ) + " )";
        stmt.executeQuery( query ).close( );
	stmt.close( );

        for ( int i = 0; i < ss.getSynonyms( ).length; i++ )
          updateWordSense( ss.getSynonyms( )[i], eID );
        for ( int i = 0; i < ss.getRelations( ).length; i++ )
          updateSemanticRelation( ss, ss.getRelations( )[i], eID );

        return;
      }

      stmt.close( );
      throw new WordNetEditException( "Synset does not exist" );
    }
    catch ( SQLException e )
    {
      throw new WordNetEditException( e.toString( ) );
    }
  }

  public void addSynset( Synset ss, String user, String pass )
    throws WordNetEditException
  {
    try
    {
      int eID = getUserID( user, pass );
      if ( eID == -1 )
        throw new WordNetEditException( "Bad username/password" );

      if ( !isFlagOn( "flag_add", eID ) )
        throw new WordNetEditException( "User cannot add" );

      Statement stmt = mysqlConnection.createStatement( );

      ResultSet res = stmt.executeQuery( "select max(SynsetKey) from Synsets" );
      res.next( );
      int newKey = res.getInt( "max(SynsetKey)" ) + 1;
      res.close( );

      String query = "insert into Synsets ( Owner, SynsetKey, Gloss, Pos, Category ) values ( " + eID + ", " + newKey + ", '" + ss.getGloss( ) + "', " + ss.getPos( ) + ", " + ss.getCategory( ) + " )";
      stmt.executeQuery( query ).close( );
      stmt.close( );

      ss.setKey( new SQLSynsetKey( newKey ) );

      for ( int i = 0; i < ss.getSynonyms( ).length; i++ )
        addWordSense( ss.getSynonyms( )[i], eID );
      for ( int i = 0; i < ss.getRelations( ).length; i++ )
        addSemanticRelation( ss, ss.getRelations( )[i], eID );
    }
    catch ( SQLException e )
    {
      throw new WordNetEditException( e.toString( ) );
    }
  }

  private void addSemanticRelation( Synset ss, SemanticRelation sr, int eID )
    throws SQLException
  {
    Statement stmt = mysqlConnection.createStatement( );
    
    int key1 = ( ( SQLSynsetKey )ss.getKey( ) ).key;
    int key2 = ( ( SQLSynsetKey )sr.key ).key;
    int type = sr.type;

    stmt.executeQuery( "insert into SemanticRelations ( Owner, Relation, SynsetKey1, SynsetKey2 ) values ( " + eID + ", " + type + ", " + key1 + ", " + key2 + " )" ).close( );
    stmt.close( );
  }

  private void addLexicalRelation( WordSense ws, LexicalRelation lr, int eID )
    throws SQLException
  {
    Statement stmt = mysqlConnection.createStatement( );
    
    int key1 = ( ( SQLWordSenseKey )ws.getKey( ) ).key;
    int key2 = ( ( SQLWordSenseKey )lr.key ).key;
    int type = lr.type;

    stmt.executeQuery( "insert into LexicalRelations ( Owner, Relation, WordSenseKey1, WordSenseKey2 ) values ( " + eID + ", " + type + ", " + key1 + ", " + key2 + " )" ).close( );
    stmt.close( );
  }

  private void updateLexicalRelation( WordSense ws, LexicalRelation lr, int eID )
    throws SQLException
  {
    addLexicalRelation( ws, lr, eID );
  }
  private void updateSemanticRelation( Synset ss, SemanticRelation sr, int eID )
    throws SQLException
  {
    addSemanticRelation( ss, sr, eID );
  }

  private void updateWordSense( WordSense ws, int eID )
    throws SQLException
  {
    Statement stmt = mysqlConnection.createStatement( );

    SQLWordSenseKey sqlwk = ( SQLWordSenseKey )ws.getKey( );    

    ResultSet res = stmt.executeQuery( "select * from WordSenses where WordSenseKey=" + ws.getKey( ) + " && Owner=" + eID );
    if ( res.next( ) )
    {
      stmt.executeQuery( "update WordSenses set Form='" + ws.getForm( ) + "', Sense=" + ws.getSense( ) + ", Polysemy=" + ws.getPolysemy( ) + ", Frequency=" + ws.getFrequency( ) + " where WordSenseKey=" + ws.getKey( ) + " && Owner=" + eID ).close( );
      for ( int i = 0; i < ws.getRelations( ).length; i++ )
        updateLexicalRelation( ws, ws.getRelations( )[i], eID );
      stmt.close( );
      return;
    }
    else
    {
      int wk = sqlwk.key;
      int sk = ( ( SQLSynsetKey )ws.getSynset( ).getKey( ) ).key;
      String form = ws.getForm( );
      int sense = ws.getSense( );
      int pol = ws.getPolysemy( );
      int freq = ws.getFrequency( );

      stmt.executeQuery( "insert into WordSenses ( Owner, WordSenseKey, SynsetKey, Form, Sense, Polysemy, Frequency ) values ( " + eID + ", " + wk + ", " + sk + ", '" + form + "', " + sense + ", " + pol + ", " + freq + " )" ).close( );

      for ( int i = 0; i < ws.getRelations( ).length; i++ )
        updateLexicalRelation( ws, ws.getRelations( )[i], eID );
      
      stmt.close( );
      return;	
    }

//    stmt.close( );

//    addWordSense( ws, eID );
  }

  private void addWordSense( WordSense ws, int eID )
    throws SQLException
  {
    Statement stmt = mysqlConnection.createStatement( );
    
    ResultSet res = stmt.executeQuery( "select max(WordSenseKey) from WordSenses" );
    res.next( );
    int wk = res.getInt( "max(WordSenseKey)" );
    res.close( );
    int sk = ( ( SQLSynsetKey )ws.getSynset( ).getKey( ) ).key;
    String form = ws.getForm( );
    int sense = ws.getSense( );
    int pol = ws.getPolysemy( );
    int freq = ws.getFrequency( );

    stmt.executeQuery( "insert into WordSenses ( Owner, WordSenseKey, SynsetKey, Form, Sense, Polysemy, Frequency ) values ( " + eID + ", " + wk + ", " + sk + ", '" + form + "', " + sense + ", " + pol + ", " + freq + " )" ).close( );

    ws.setKey( new SQLWordSenseKey( wk ) );

    for ( int i = 0; i < ws.getRelations( ).length; i++ )
      addLexicalRelation( ws, ws.getRelations( )[i], eID );

    stmt.close( );
  }

  public void removeSynset( SynsetKey sk, String reason, String user, String pass )
    throws WordNetEditException
  {
    try
    {
      int eID = getUserID( user, pass );
      if ( eID == -1 )
        throw new WordNetEditException( "Bad username/password" );

      if ( !isFlagOn( "flag_remove", eID ) )
        throw new WordNetEditException( "User cannot remove" );

      SQLSynsetKey sqlsk = ( SQLSynsetKey )sk;

      Statement stmt = mysqlConnection.createStatement( );
      ResultSet res = stmt.executeQuery( "select Owner from Synsets where SynsetKey=" + sqlsk.key + " && Owner=" + eID );
      if ( res.next( ) )
      {
        res.close( );
	
        stmt.executeQuery( "delete from Synsets where SynsetKey=" + sqlsk.key + " && Owner=" + eID ).close( );

	res = stmt.executeQuery( "Select WordSenseKey from WordSenses where SynsetKey=" + sqlsk.key + " && Owner=" + eID );

	while ( res.next( ) )
	  stmt.executeQuery( "delete from LexicalRelations where WordSenseKey1=" + res.getInt( "WordSenseKey" ) + " || WordSenseKey2=" + res.getInt( "WordSenseKey" ) + " && Owner=" + eID ).close( );
	res.close( );

	stmt.executeQuery( "delete from WordSenses where SynsetKey=" + sqlsk.key + " && Owner=" + eID ).close( );

	stmt.executeQuery( "delete from SemanticRelations where SynsetKey1=" + sqlsk.key + " || SynsetKey2=" + sqlsk.key + " && Owner=" + eID ).close( );
      }
      res.close( );

      stmt.executeQuery( "insert into RemovedSynsets ( SynsetKey, Owner, Reason ) values ( " + sqlsk.key + ", " + eID + ", '" + reason + "' )" ).close( );
      stmt.close( );
     
    }
    catch ( SQLException e )
    {
      throw new WordNetEditException( e.toString( ) );
    }
  }

  public void reassignSynset( SynsetKey sk, int oldOwner, String user, String pass )
    throws WordNetEditException
  {
    try
    {
      Statement stmt = mysqlConnection.createStatement( );
      ResultSet res = stmt.executeQuery( "select flag_reassign, editor_id from wneditors where user_name=" + user + " && password=password(" + pass + ")" );

      if ( !res.next( ) )
      {
        //authorization failed
        res.close( );
        return;
      }
      if ( !res.getBoolean( "flag_reassign" ) )
      {
        //user cannot reassign
        res.close( );
        return;
      }

      int eID = res.getInt( "editor_id" );

      res.close( );

      SQLSynsetKey sqlsk = ( SQLSynsetKey )sk;

      res = stmt.executeQuery( "select * from RemovedSynsets where SynsetKey=" + sqlsk.key + " && Owner=" + oldOwner );
      if ( res.next( ) )
      {
        res.close( );
	stmt.executeQuery( "delete from Synsets where SynsetKey=" + sqlsk.key + " && Owner=" + eID ).close( );
	stmt.executeQuery( "delete from RemovedSynsets where SynsetKey=" + sqlsk.key + " && Owner=" + oldOwner ).close( );
	stmt.close( );
	return;
      }

      stmt.executeQuery( "update Synsets set Owner=" + eID + " where SynsetKey=" + sqlsk.key + " && Owner=" + oldOwner ).close( );

      res.close( );
      stmt.close( );
    }
    catch ( SQLException e )
    {
      throw new WordNetEditException( e.toString( ) );
    }
  }
}
